// <copyright file="SqlGenerator.cs" company="DotNetSide">
// Copyright (c) 2008 All Right Reserved
// </copyright>
// <author>fabio.cozzolino</author>
// <date>30/06/2008 18.09.35</date>

namespace DotNetSide.SqlScriptGenerator {
	using System;
	using System.Collections.Generic;
	using System.Text;
	using System.Data.SqlClient;
	using System.Data;
	using System.Diagnostics;
	using System.Globalization;

	public class SqlGenerator {
		private DateTimeFormatInfo dateFormatInfo;
		private string connectionString, databaseName;
		private SqlGeneratorOptions options;
		private StringBuilder script;

		public string Script {
			get { return this.script.ToString(); }
		}

		public SqlGenerator(string connectionString, string databaseName, SqlGeneratorOptions options) {
			this.script = new StringBuilder();
			this.connectionString = connectionString;
			this.databaseName = databaseName;
			this.dateFormatInfo = (DateTimeFormatInfo)CultureInfo.CurrentCulture.DateTimeFormat.Clone();
			this.dateFormatInfo.TimeSeparator = ":";
			this.options = options;
		}

		public void Generate() {
			this.ValidateOptions();

			// erase last script
			this.script.Remove(0, script.Length);

			// begin new script
			this.script.AppendLine();
			if (this.options.UseTransaction) {
				script.AppendLine("BEGIN TRAN");
			}
			this.script.AppendLine("USE [" + this.databaseName + "]");
			this.script.AppendLine();

			foreach (string tableName in this.options.Tables) {
				this.GenerateTable(tableName);
			}

			if (this.options.UseTransaction) {
				this.script.AppendLine();
				this.script.AppendLine("COMMIT");
				this.script.AppendLine();
			}
		}

		private void ValidateOptions() {
			if (this.options.QueryType != TypeOfQuery.InsertOnly
				& this.options.GenerateDeleteStatement) {
				throw new ApplicationException("The selected options will always DELETE ALL current records " +
					"and ADD ALL new records.\tPlease check the options.");
			}
		}

		private void GenerateTable(string tableName) {
			if (!this.HasRows(tableName)) {
				return;
			}

			this.script.AppendLine("--");
			this.script.AppendLine("--  Generate " + tableName);
			this.script.AppendLine("--");
			this.script.AppendLine();

			this.script.AppendLine("-- Disable all check on table");
			this.script.AppendLine("ALTER TABLE " + tableName + " NOCHECK CONSTRAINT ALL");
			this.script.AppendLine();

			if (this.options.GenerateDeleteStatement) {
				this.script.AppendLine("-- Remove first all records");
				this.script.AppendLine("DELETE FROM " + tableName);
				this.script.AppendLine();
			}

			this.script.AppendLine("-- Insert new records");
			SqlConnection conn = new SqlConnection(this.connectionString);
			try {
				conn.Open();
				DataTable schema = DatabaseHelper.GetTableSchema(this.databaseName, tableName, conn);

				string[] keys;

				bool hasKeys;
				DatabaseHelper.GetTableKeys(this.databaseName, tableName, conn, out keys, out hasKeys);

				if (hasKeys) {
					this.script.AppendLine();
					this.script.AppendLine("SET IDENTITY_INSERT " + tableName + " ON");
					this.script.AppendLine();
				}

				this.SaveSingleRow(tableName, conn, schema, keys);
				conn.Close();

				if (hasKeys) {
					this.script.AppendLine();
					this.script.AppendLine("SET IDENTITY_INSERT " + tableName + " OFF");
					this.script.AppendLine();
				}
			} catch (Exception) {
				throw;
			} finally {
				if (conn.State == ConnectionState.Open) {
					conn.Close();
				}
			}

			script.AppendLine("-- Enable all check on table");
			script.AppendLine("ALTER TABLE " + tableName + " CHECK CONSTRAINT ALL");
			script.AppendLine();

			script.AppendLine();
		}

		private bool HasRows(string tableName) {
			bool hasRows;
			SqlConnection conn = new SqlConnection(this.connectionString);
			try {
				SqlCommand cmd = conn.CreateCommand();
				cmd.CommandText = "SELECT COUNT(*) FROM " + tableName;
				conn.Open();
				int numOfRows = (int)cmd.ExecuteScalar();
				if (numOfRows > 0) {
					hasRows = true;
				}
				else {
					hasRows = false;
				}
			} finally {
				if (conn.State == ConnectionState.Open) {
					conn.Close();
				}
			}
			return hasRows;
		}

		private void SaveSingleRow(string tableName, SqlConnection conn, DataTable schema, string[] keys) {
			SqlCommand cmd = conn.CreateCommand();
			cmd.CommandText = "SELECT * FROM " + tableName;
			SqlDataReader reader = cmd.ExecuteReader();
			while (reader.Read()) {
				if (this.options.QueryType == TypeOfQuery.InsertOnly) {
					this.InsertOnly(tableName, schema, reader);
				}
				if (this.options.QueryType == TypeOfQuery.InsertOrUpdate) {
					this.InsertOrUpdate(tableName, schema, keys, reader);
				}
				if (this.options.QueryType == TypeOfQuery.InsertOnlyNewRecords) {
					this.InsertOnlyNewRecords(tableName, schema, keys, reader);
				}
				if (this.options.QueryType == TypeOfQuery.UpdateOnly) {
					this.UpdateOnly(tableName, schema, keys, reader);
				}
			}
			reader.Close();
		}

		private void UpdateOnly(string tableName, DataTable schema, string[] keys, SqlDataReader reader) {
			this.script.Append("IF EXISTS (");
			this.AppendSelectCommand(tableName, reader, keys);
			this.script.Append(")");
			this.script.AppendLine();
			this.script.AppendLine("BEGIN");
			this.script.Append("    ");
			this.AppendUpdateCommand(tableName, schema, reader, keys);
			this.script.AppendLine("END");
			this.script.AppendLine();
		}

		private void InsertOnlyNewRecords(string tableName, DataTable schema, string[] keys, SqlDataReader reader) {
			this.script.Append("IF NOT EXISTS (");
			this.AppendSelectCommand(tableName, reader, keys);
			this.script.Append(")");
			this.script.AppendLine();
			this.script.AppendLine("BEGIN");
			this.script.Append("    ");
			this.AppendInsertCommand(tableName, schema, reader);
			this.script.AppendLine("END");
			this.script.AppendLine();
		}

		private void InsertOnly(string tableName, DataTable schema, SqlDataReader reader) {
			this.AppendInsertCommand(tableName, schema, reader);
		}

		private void InsertOrUpdate(string tableName, DataTable schema, string[] keys, SqlDataReader reader) {
			this.script.Append("IF EXISTS (");
			this.AppendSelectCommand(tableName, reader, keys);
			this.script.Append(")");
			this.script.AppendLine();
			this.script.AppendLine("BEGIN");
			this.script.Append("    ");
			this.AppendUpdateCommand(tableName, schema, reader, keys);
			this.script.AppendLine("END");
			this.script.AppendLine("ELSE");
			this.script.AppendLine("BEGIN");
			this.script.Append("    ");
			this.AppendInsertCommand(tableName, schema, reader);
			this.script.AppendLine("END");
			this.script.AppendLine();
		}

		private void AppendUpdateCommand(string tableName, DataTable schema, SqlDataReader reader, string[] keys) {
			StringBuilder values = new StringBuilder();
			foreach (DataRow row in schema.Rows) {
				string columnName = (string)row[3];
				if (Array.BinarySearch<string>(keys, columnName) < 0) {
					if (values.Length > 0) {
						values.Append(", ");
					}
					values.Append("[" + columnName + "] = ");
					this.SaveSingleColValue(reader, values, columnName);
				}
			}

			StringBuilder keysClause = new StringBuilder();
			this.AppendKeysClause(reader, keys, keysClause);

			this.script.AppendFormat("UPDATE {0} SET {1} WHERE {2}", tableName, values, keysClause);
			this.script.AppendLine();
		}

		private void AppendSelectCommand(string tableName, SqlDataReader reader, string[] keys) {
			StringBuilder values = new StringBuilder();
			this.AppendKeysClause(reader, keys, values);
			this.script.AppendFormat("SELECT * FROM {0} WHERE {1}", tableName, values);
		}

		private void AppendKeysClause(SqlDataReader reader, string[] keys, StringBuilder values) {
			foreach (string key in keys) {
				if (values.Length > 0) {
					values.Append(" AND ");
				}
				values.Append("[" + key + "] = ");
				this.SaveSingleColValue(reader, values, key);
			}
		}

		private void AppendInsertCommand(string tableName, DataTable schema, SqlDataReader reader) {
			StringBuilder columns = this.GetInsertColumnsName(schema);
			StringBuilder values = new StringBuilder();
			foreach (DataRow row in schema.Rows) {
				if (values.Length > 0) {
					values.Append(", ");
				}

				this.SaveSingleColValue(reader, values, (string)row[3]);
			}

			this.script.AppendFormat("INSERT INTO {0} {1} VALUES ({2})", tableName, columns, values);
			this.script.AppendLine();
		}

		/// <summary>
		/// Saves the single col value.
		/// </summary>
		/// <param name="reader">The reader.</param>
		/// <param name="values">The values.</param>
		/// <param name="columnName">Name of the column.</param>
		private void SaveSingleColValue(SqlDataReader reader, StringBuilder values, string columnName) {
			object columnValue = reader[columnName];

			////////if (columnValue is byte[])
			////////{
			////////    byte[] buffer = columnValue as byte[];
			////////    values.Append("'");
			////////    values.Append(new System.Data.SqlTypes.SqlBinary(buffer).ToString());
			////////    values.Append("'");
			////////}
			if (columnValue is string || columnValue is Guid) {
				values.Append("'");
				values.Append(columnValue.ToString().Replace("'", ""));
				values.Append("'");
			}
			else if (columnValue is DateTime) {
				values.Append("'");
				//this was creating dates in the wrong format
				//values.Append(((DateTime)columnValue).ToString("yyyyMMdd HH:mm:ss", dateFormatInfo));
				//create dates using the correct format
				dateFormatInfo.FullDateTimePattern = "yyyyMMdd HH:mm:ss";
				values.Append(((DateTime)columnValue).ToString("yyyy-MM-dd HH:mm:ss"));
				values.Append("'");
			}
			else if (columnValue is bool) {
				values.Append((bool)columnValue ? '1' : '0');
			}
			else {
				if (columnValue is DBNull) {
					columnValue = "null";
				}
				values.Append(columnValue);
			}
		}

		private StringBuilder GetInsertColumnsName(DataTable schema) {
			StringBuilder columns = new StringBuilder();
			columns.Append("(");
			foreach (DataRow row in schema.Rows) {
				columns.AppendFormat("[{0}], ", (string)row[3]);
			}
			columns.Remove(columns.Length - 2, 2);
			columns.Append(")");
			return columns;
		}
	}
}
